const express = require('express');
const router = express.Router();
const mysql = require('mysql');
const bcrypt = require('bcrypt');
const constant = require('../data/constant');
const auth = require('../middleware/auth');

// 构造SQL语句的方法
function getSql(req){
    let sql = '';
    switch(req.body.select){
        case '1':sql = "select * from files where name like ? and status = ?;";break;
        case '2':sql = "select * from files where (course like ? or course = '通用') and status = ?;";break;
        case '3':sql = "select * from files where stage like ? and status = ?;";break;
        case '4':sql = "select * from files where type like ? and status = ?;";break;
        case '5':sql = "select * from files where tags like ? and status = ?;";break;
        case '6':sql = "select * from project where name like ?;";break;
        default:break;
    }
    return sql;
}
// 搜索接口
router.post('/get_search_result',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = getSql(req);
            let  sqlParams = [
                "%"+req.body.search_input+"%",
                3
            ];
            if(req.body.search_input == '*'){
                sql = "select * from project where isFinished = ?;"
                sqlParams = [
                    true
                ];
            }
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    console.log(res_data);
                    if(res_data.length >= 1){
                        res.send({
                            res:{
                                ok:1,
                                msg:"为您找到"+res_data.length+"条结果！",
                                resultType:req.body.select === '6' ? "project" : "normal",
                                resultList:res_data
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"很抱歉，未找到任何结果！"
                            }
                        })
                    } 
                }
            });
            connection.release();
        }
    });
});

// 资源版本更新的接口
router.post('/file_update',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = "UPDATE files SET path = ?,originalname = ?,version = ? WHERE file_id = ?;"+
            "INSERT INTO"+
            " history_version(`file_id`,`originalname`,`version`,`uploader`,`update_log`,`path`,`size`)"+
            " VALUES(?,?,?,?,?,?,?);"
            var  sqlParams = [
                req.body.path,
                req.body.originalname,
                parseFloat(req.body.version),
                req.body.file_id,
                req.body.file_id,
                req.body.originalname,
                req.body.version,
                req.body.uploader,
                req.body.desc,
                req.body.path,
                req.body.size
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    console.log(res_data);
                    if(res_data[0].affectedRows === 1 && res_data[1].affectedRows === 1){
                        res.send({
                            res:{
                                ok:1,
                                msg:"提交成功！您更新的资源正在等待管理员审核，审核结果可以在'我的——个人中心——消息中心'中找到"
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"提交失败，请稍后再试！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});

module.exports = router;